package com.blue.excel.util;

import com.blue.excel.model.CellStyleInfo;
import com.blue.excel.model.ColumnField;
import com.blue.excel.type.Alignment;
import com.blue.excel.type.CellType;
import com.blue.excel.type.CellTypeHelper;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by ChenYaHui on 2017/8/2.
 */
public class ExcelHelper {
    Workbook workbook;

    CellStyleMaker cellStyleMaker;

    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    private Sheet currentSheet;

    public ExcelHelper(Workbook workbook) {
        this.workbook = workbook;
        cellStyleMaker = new CellStyleMaker(workbook);
    }

    public void setCurrentSheet(Sheet currentSheet) {
        this.currentSheet = currentSheet;
    }

    protected void copyCellValue(Cell target,Cell source){
        copyCellValue(target,source,true);
    }

    protected void copyCellValue(Cell target,Cell source,boolean copyStyle){
        int cellType = source.getCellType();
        if(copyStyle){
            CellStyle sourceCellStyle = source.getCellStyle();
            target.setCellStyle(sourceCellStyle);
        }
        switch (cellType){
            case Cell.CELL_TYPE_STRING:
                String strValue = source.getStringCellValue();
                if(strValue != null){
                    target.setCellValue(strValue);
                }
                break;
            case Cell.CELL_TYPE_NUMERIC:
                Double doubleValue = source.getNumericCellValue();
                if(doubleValue != null){
                    target.setCellValue(doubleValue);
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                Boolean booleanValue = source.getBooleanCellValue();
                if(booleanValue != null){
                    target.setCellValue(booleanValue);
                }
                break;
        }
    }

    public void fillCell(Cell cell,Object value,Alignment alignment,ColumnField columnField){
        fillCell(cell,value,columnField);
        switch (alignment){
            case Left:
                setAlignLeft(cell);
                break;
            case Center:
                setAlignCenter(cell);
                break;
            case Right:
                setAlignRight(cell);
                break;
            case Auto:
                break;
        }
    }

    public void fillCell(Cell cell,Object value,Alignment alignment){
        fillCell(cell,value);
        switch (alignment){
            case Left:
                setAlignLeft(cell);
                break;
            case Center:
                setAlignCenter(cell);
                break;
            case Right:
                setAlignRight(cell);
                break;
            case Auto:
                break;
        }
    }

    private void fillCell(Cell cell, Object value,ColumnField columnField){
        value = columnField.valueSetInterceptor.change(value);
        fillCell(cell,value);
    }


    private void fillCell(Cell cell, Object value){
        if(value == null){
            return ;
        }
        CellType cellType = CellTypeHelper.getCellType(value);
        String strValue = String.valueOf(value);
        switch (cellType){
            case Auto:
            case STRING:
                cell.setCellValue(String.valueOf(value));
                setAlignLeft(cell);
                break;
            case NUMBER:
                cell.setCellValue(Double.parseDouble(String.valueOf(value)));
                setAlignRight(cell);
                break;
            case PERCENTUM:
                String strValue_1 = strValue.substring(0, strValue.length() - 1);
                String strValue_2 = strValue_1.replaceAll(",", "");
                cell.setCellValue(Double.parseDouble(strValue_2) / 100);
                setPercent(cell);
                setAlignRight(cell);
                break;
            case DATE:
                Date dateValue = (Date) value;
                String dateStrValue = simpleDateFormat.format(dateValue);
                cell.setCellValue(dateStrValue);
                setAlignCenter(cell);
                break;
            case BOOLEAN:
                Boolean booleanValue = Boolean.parseBoolean(strValue);
                cell.setCellValue(booleanValue);
                setAlignCenter(cell);
                break;

        }
    }


    public void setPercent(Cell cell){
        CellStyleInfo cellStyleInfo = new CellStyleInfo(cell.getCellStyle());
        cellStyleInfo.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        cell.setCellStyle(cellStyleMaker.getCellStyle(cellStyleInfo));
    }

    public void setAlignLeft(Cell cell){
        CellStyleInfo cellStyleInfo = new CellStyleInfo(cell.getCellStyle());
        cellStyleInfo.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cell.setCellStyle(cellStyleMaker.getCellStyle(cellStyleInfo));
    }
    public void setAlignCenter(Cell cell){
        CellStyleInfo cellStyleInfo = new CellStyleInfo(cell.getCellStyle());
        cellStyleInfo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cell.setCellStyle(cellStyleMaker.getCellStyle(cellStyleInfo));
    }

    public void setAlignRight(Cell cell){
        CellStyleInfo cellStyleInfo = new CellStyleInfo(cell.getCellStyle());
        cellStyleInfo.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        cell.setCellStyle(cellStyleMaker.getCellStyle(cellStyleInfo));
    }

    public void setBgColorGray(Cell cell){
        CellStyleInfo cellStyleInfo = new CellStyleInfo(cell.getCellStyle());
        cellStyleInfo.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cell.setCellStyle(cellStyleMaker.getCellStyle(cellStyleInfo));
    }

    public void setBgColorWhite(Cell cell){
        CellStyleInfo cellStyleInfo = new CellStyleInfo(cell.getCellStyle());
        cellStyleInfo.setFillForegroundColor(HSSFColor.WHITE.index);
        cell.setCellStyle(cellStyleMaker.getCellStyle(cellStyleInfo));
    }

    public void setBigTitle(Cell cell){
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

    public CellStyle getCellStyle(CellStyleInfo cellStyleInfo){
        return cellStyleMaker.getCellStyle(cellStyleInfo);
    }


    public int getContentLength(Cell cell){
        int cellType = cell.getCellType();
        String strValue = null;
        switch (cellType){
            case Cell.CELL_TYPE_NUMERIC:
                strValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                strValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                strValue = String.valueOf(cell.getBooleanCellValue());
                break;
        }
        if(strValue == null){
            return 0;
        }
        return strValue.getBytes().length * 256;
    }

//    public boolean isNum(Object object) {
//        try {
//            new BigDecimal(String.valueOf(object));
//            return true;
//        } catch (Exception e) {
//            return false;
//        }
//    }

//    public boolean isBoolean(String str){
//        try {
//            Boolean.parseBoolean(str);
//            return true;
//        } catch (Exception e) {
//            return false;
//        }
//    }

    protected Row getOrCreateRow(int rowIndex){
        Row row = currentSheet.getRow(rowIndex);
        if(row == null){
            row = currentSheet.createRow(rowIndex);
        }
        return row;
    }

    protected Cell getOrCreateCell(Row row,int index){
        Cell cell = row.getCell(index);
        if(cell == null){
            cell = row.createCell(index);
        }
        return cell;
    }


    protected void pushColumnsRightFrom(int index){
        if(index < 0)
            return ;
        int mergeRegionNum = currentSheet.getNumMergedRegions();
        List<CellRangeAddress> mergeRegions = new ArrayList<>();
        for(int i = mergeRegionNum - 1; i >= 0; i --){
            CellRangeAddress mergedRegion = currentSheet.getMergedRegion(i);
            if(mergedRegion.getFirstColumn() >= index){
                mergeRegions.add(mergedRegion);
                currentSheet.removeMergedRegion(i);
            }
        }

        for(int i = currentSheet.getFirstRowNum(); i <= currentSheet.getLastRowNum(); i++){
            Row currentRow = currentSheet.getRow(i);
            int n = 0;
            for(int j = currentRow.getLastCellNum(); j > index; j--){
                Cell targetCell = null;
                if(n == 0){
                    targetCell = currentRow.createCell(j);
                }else{
                    targetCell = currentRow.getCell(j);
                }
                n ++;
                Cell sourceCell = currentRow.getCell(j - 1);
                if(sourceCell == null){
                    sourceCell = currentRow.createCell(j - 1);
                }
                copyCellValue(targetCell, sourceCell);
                sourceCell.setCellValue("");
            }
        }

        for(CellRangeAddress cellRangeAddress : mergeRegions){
            cellRangeAddress.setFirstColumn(cellRangeAddress.getFirstColumn() + 1);
            cellRangeAddress.setLastColumn(cellRangeAddress.getLastColumn() + 1);
            currentSheet.addMergedRegion(cellRangeAddress);
        }
    }

    protected void pushColumnsRightFrom(int index,int step){
        if(index < 0)
            return ;
        int mergeRegionNum = currentSheet.getNumMergedRegions();
        List<CellRangeAddress> mergeRegions = new ArrayList<>();
        for(int i = mergeRegionNum - 1; i >= 0; i --){
            CellRangeAddress mergedRegion = currentSheet.getMergedRegion(i);
            if(mergedRegion.getFirstColumn() >= index){
                mergeRegions.add(mergedRegion);
                currentSheet.removeMergedRegion(i);
            }
        }

        for(int i = currentSheet.getFirstRowNum(); i <= currentSheet.getLastRowNum(); i++){
            Row currentRow = currentSheet.getRow(i);
            for(int j = currentRow.getLastCellNum() + step - 1; j > index + step -1; j--){
                Cell targetCell = getOrCreateCell(currentRow,j);
                Cell sourceCell = getOrCreateCell(currentRow,j - step);
                copyCellValue(targetCell, sourceCell);
                sourceCell.setCellValue("");
            }
        }

        for(CellRangeAddress cellRangeAddress : mergeRegions){
            cellRangeAddress.setFirstColumn(cellRangeAddress.getFirstColumn() + step);
            cellRangeAddress.setLastColumn(cellRangeAddress.getLastColumn() + step);
            currentSheet.addMergedRegion(cellRangeAddress);
        }
    }


    protected int getMergeRegion(int firstRow, int lastRow, int firstCol, int lastCol){
        int mergeRegionNum = currentSheet.getNumMergedRegions();
        for(int i = 0; i < mergeRegionNum; i++){
            CellRangeAddress mergedRegion = currentSheet.getMergedRegion(i);
            if(mergedRegion.getFirstColumn() == firstCol && mergedRegion.getLastColumn() == lastCol && mergedRegion.getFirstRow() == firstRow && mergedRegion.getLastRow() == lastRow){
                return i;
            }
        }
        return -1;
    }

    protected void mergeRegion(int firstRow, int lastRow, int firstCol, int lastCol){
        if(firstRow > lastRow || firstCol > lastCol){
            return ;
        }
        if(firstRow == lastRow && firstCol == lastCol){
            return;
        }
        CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow,lastRow,firstCol,lastCol);
        currentSheet.addMergedRegion(cellRangeAddress);
    }

    protected boolean isInMergeRegion(int row,int col){
        int mergeRegionNum = currentSheet.getNumMergedRegions();
        for(int i = 0; i < mergeRegionNum; i++){
            CellRangeAddress mergedRegion = currentSheet.getMergedRegion(i);
            if(mergedRegion.getFirstColumn() <= col && mergedRegion.getLastColumn() >= col && mergedRegion.getFirstRow() <= row && mergedRegion.getLastRow() >= row){
                return true;
            }
        }
        return false;
    }

    public int getMergeRegion(int row,int col){
        int mergeRegionNum = currentSheet.getNumMergedRegions();
        for(int i = 0; i < mergeRegionNum; i++){
            CellRangeAddress mergedRegion = currentSheet.getMergedRegion(i);
            if(mergedRegion.getFirstColumn() <= col && mergedRegion.getLastColumn() >= col && mergedRegion.getFirstRow() <= row && mergedRegion.getLastRow() >= row){
                return i;
            }
        }
        return -1;
    }

}
